package com.sxt.demo;

import java.util.Arrays;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import com.sxt.dao.HibernateSessionFactory;
import com.sxt.entity.City;
import com.sxt.entity.SearchBean;
import com.sxt.entity.User;
import com.sxt.entity.UserBean;

public class Demo1 {
	
	/**
	 * HQL语句入门
	 */
	@Test
	public void test1() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "from User";
		Query query = session.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if (session != null) {
			session.close();
			session = null;
		}
	}
	
	/**
	 * 条件查询
	 */
	@Test
	public void test2() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "from User u where u.age > 20 and u.city.name like '_海%'";
		Query query = session.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if (session != null) {
			session.close();
			session = null;
		}
	}
	
	/**
	 * 多列查询
	 */
	@Test
	public void test3() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "select u.uname, u.age, u.city.name from User u";
		Query query = session.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<Object[]> list = query.list();
		for (Object[] obj : list) {
			System.out.println(obj[0] + "," + obj[1] + "," + obj[2]);
		}
		if (session != null) {
			session.close();
			session = null;
		}
	}
	
	/**
	 * 查询某对象的所有属性不等于查询该对象
	 * 运行出错
	 */
	@Test
	public void test4() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "select u.uname, u.age, u.uid, u.city from User u";
		Query query = session.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if (session != null) {
			session.close();
			session = null;
		}
	}
	
	/**
	 * 错误查询方式,同时查询c.name和c.users
	 */
	@Test
	public void test5() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "select c.name, c.users from City c";
		Query query = session.createQuery(hql);
		@SuppressWarnings({ "rawtypes", "unused" })
		List list = query.list();
//		for (User user : list) {
//			System.out.println(user.getUname() + "," + user.getCity().getName());
//		}
		if (session != null) {
			session.close();
			session = null;
		}
	}
	
	/**
	 * '?'占位符
	 */
	@Test
	public void test6() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "from User u where u.age > ? and u.city.name like ?";
		Query query = session.createQuery(hql);
		query.setInteger(0, 20);
		query.setString(1, "_海");
		@SuppressWarnings("unchecked")
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if (session != null) {
			session.close();
			session = null;
		}
	}
	
	/**
	 * 命名参数
	 */
	@Test
	public void test7() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "from User u where u.age > :age and u.city.name like :name";
		Query query = session.createQuery(hql);
		query.setInteger("age", 20);
		query.setString("name", "_海");
		@SuppressWarnings("unchecked")
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if (session != null) {
			session.close();
			session = null;
		}
	}
	
	/**
	 * 命名参数封装
	 */
	@Test
	public void test8() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "from User u where u.age > :age and u.city.name like :name";
		Query query = session.createQuery(hql);
		query.setProperties(new SearchBean(20, "%海%"));
		@SuppressWarnings("unchecked")
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if (session != null) {
			
			session.close();
			session = null;
		}
	}
	
	/**
	 * 聚合函数
	 */
	@Test
	public void test9() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "select avg(u.age), max(u.age) from User u";
		Query query = session.createQuery(hql);
		Object[] objs = (Object[]) query.uniqueResult();
		System.out.println(Arrays.toString(objs));
	}
	
	/**
	 * 聚合函数分组查询
	 */
	@Test
	public void test10() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "select u.city, avg(u.age), max(u.age) from User u group by u.city";
		Query query = session.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<Object[]> list = query.list();
		
		for (Object[] objs : list) {
			System.out.println(((City)objs[0]).getName()+"：平均年龄"+objs[1] + "岁,最大年龄" + objs[2]+"岁");
		}
	}
	
	/**
	 * 聚合函数分组条件查询
	 */
	@Test
	public void test11() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "select u.city, avg(u.age), max(u.age) from User u group by u.city having max(u.age) > 25";
		Query query = session.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<Object[]> list = query.list();
		
		for (Object[] objs : list) {
			System.out.println(((City)objs[0]).getName()+"：平均年龄"+objs[1] + "岁,最大年龄" + objs[2]+"岁");
		}
	}
	
	/**
	 * 分页查询
	 */
	@Test
	public void test12demo() {
		test12(2, 2);
	}
	public void test12(int pageNum, int pageSize) {
		Session session = HibernateSessionFactory.getSession();
		String hql = "from User";
		Query query = session.createQuery(hql);
		query.setFirstResult(pageSize * (pageNum-1));
		query.setMaxResults(pageSize);
		@SuppressWarnings("unchecked")
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user.getUname() + "," + user.getCity().getName());
		}
		if (session != null) {
			session.close();
			session = null;
		}
	}
	
	/**
	 * 外连接练习，查询没有人的城市
	 */
	@Test
	public void test13() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "from City c left join fetch c.users u where u is null";
		Query query = session.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<City> list = query.list();
		for (City city : list) {
			System.out.println(city.getName());
		}
		session.close();
	}
	
	/**
	 * 命名查询
	 */
	@Test
	public void test14() {
		Session session = HibernateSessionFactory.getSession();
		Query query = session.getNamedQuery("com.sxt.entity.City.myquery1"); //在class内部
		//Query query = session.getNamedQuery("myquery2"); //在class外部
		@SuppressWarnings("unchecked")
		List<City> list = query.list();
		for (City city : list) {
			System.out.println(city.getName());
		}
		session.close();
	}
	
	/**
	 * 动态实例化查询结果
	 */
	@Test
	public void test15() {
		Session session = HibernateSessionFactory.getSession();
		String hql = "select new com.sxt.entity.UserBean(u.uname, u.city.name) from User u";
		Query query = session.createQuery(hql);
		@SuppressWarnings("unchecked")
		List<UserBean> list = query.list();
		for (UserBean userBean : list) {
			System.out.println(userBean);
		}
		session.close();
	}
	
	/**
	 * 本地SQL查询直接用法
	 */
	@Test
	public void test16() {
		Session session = HibernateSessionFactory.getSession();
		String sql = "select * from user";
		SQLQuery sq = session.createSQLQuery(sql).addEntity(User.class);
		@SuppressWarnings("unchecked")
		List<User> list = sq.list();
		for (User user : list) {
			System.out.println(user.getUname());
		}
		session.close();
	}
	
	/**
	 * 本地SQL更改直接用法
	 */
	@Test
	public void test17() {
		Session session = HibernateSessionFactory.getSession();
		String sql = "update user set uname = '张岂钊' where uid = 9";
		Transaction tx = session.beginTransaction();
		SQLQuery sq = session.createSQLQuery(sql);
		sq.executeUpdate();
		tx.commit();
		session.close();
	}

	/**
	 * 命名SQL用法
	 */
	@Test
	public void test18() {
		Session session = HibernateSessionFactory.getSession();
		Query query = session.getNamedQuery("mysql");
		@SuppressWarnings("unchecked")
		List<User> list = query.list();
		for (User user : list) {
			System.out.println(user.getUname());
		}
		session.close();
	}
}
